package com.databaseconnectionmobilapi.util.util;

import com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler;
import com.databaseconnectionmobilapi.constant.Storage;
import com.databaseconnectionmobilapi.domain.dto.DataDTO;
import com.databaseconnectionmobilapi.domain.entity.CharsetEntity;
import com.databaseconnectionmobilapi.domain.entity.CollationEntity;
import com.databaseconnectionmobilapi.domain.entity.FieldEntity;
import com.databaseconnectionmobilapi.service.RedisService;
import org.hibernate.sql.ordering.antlr.CollationSpecification;
import org.springframework.cglib.beans.BeanGenerator;
import org.springframework.cglib.beans.BeanMap;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.swing.text.StyledEditorKit;
import java.sql.*;
import java.util.*;

/**
 * @author Fall
 */
@Component
public class DbUtil {
    public static void establishMySQLConnection(Integer id, String ip, String port, String account, String password) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":" + port, account, password);
            Storage.CONNECTION_STORAGE.put(id, connection);
            Storage.CONNECTIONS++;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void freeMySQLConnection(Integer id) {
        try {
            ((Connection) Storage.CONNECTION_STORAGE.get(id)).close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Storage.CONNECTION_STORAGE.remove(id);
        Storage.CONNECTIONS--;
    }

    public static Object selectAllDb(Integer id) {
        List<String> databases = new ArrayList<>();
        Statement statement = null;
        try {
            statement = ((Connection) Storage.CONNECTION_STORAGE.get(id)).createStatement();
            ResultSet resultSet = statement.executeQuery("show databases");
            while (resultSet.next()) {
                databases.add(resultSet.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return databases;
    }

    public static Object selectAllTable(Integer id, String dbName) {
        List<String> tables = new ArrayList<>();
        Statement statement = null;
        try {
            statement = ((Connection) Storage.CONNECTION_STORAGE.get(id)).createStatement();
            statement.execute("use " + dbName);
            ResultSet resultSet = statement.executeQuery("show tables");
            while (resultSet.next()) {
                tables.add(resultSet.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return tables;
    }

    public static Object selectTable(Integer id, String dbName, String tableName, Integer page, Integer pageSize) {
        List<Map<String, Object>> tableData = new ArrayList<>();
        List<FieldEntity> fieldEntities = new ArrayList<>();

        Statement statement = null;
        try {
            statement = ((Connection) Storage.CONNECTION_STORAGE.get(id)).createStatement();
            statement.execute("use " + dbName);

            ResultSet columnSet = statement.executeQuery("desc " + tableName);

            // 取出所有字段名
            while (columnSet.next()) {
                fieldEntities.add(FieldEntity
                        .builder()
                        .name(columnSet.getString(1))
                        .type(columnSet.getString(2))
                        .build());
            }

            ResultSet resultSet = statement.executeQuery("select * from " + tableName + " limit " + pageSize + " offset " + page * pageSize);

            int key = 0;
            while (resultSet.next()) {
                Map<String, Object> table = new HashMap<>();
                table.put("key", key++);
                for (int i = 1; i <= fieldEntities.size(); i++) {
                    table.put(fieldEntities.get(i - 1).getName(), resultSet.getObject(i));
                }

                tableData.add(table);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        List<Object> list = new ArrayList<>();
        list.add(fieldEntities);
        list.add(tableData);

        return list;
    }

    public static List<CharsetEntity> selectAllCharset(Integer id) {
        List<CharsetEntity> charsetEntities = new ArrayList<>();

        Statement statement = null;
        try {
            statement = ((Connection) Storage.CONNECTION_STORAGE.get(id)).createStatement();
            ResultSet resultSet = statement.executeQuery("SHOW CHARACTER SET");

            // 取出所有字段名
            while (resultSet.next()) {
                charsetEntities.add(CharsetEntity
                        .builder()
                        .charset(resultSet.getString("Charset"))
                        .defaultCollation(resultSet.getString("Default collation"))
                        .build());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return charsetEntities;
    }

    public static List<CollationEntity> selectAllCollection(Integer id, String collation) {
        List<CollationEntity> collationEntities = new ArrayList<>();

        Statement statement = null;
        try {
            statement = ((Connection) Storage.CONNECTION_STORAGE.get(id)).createStatement();
            ResultSet resultSet = statement.executeQuery("SHOW COLLATION LIKE '" + collation + "%'");

            // 取出所有字段名
            while (resultSet.next()) {
                collationEntities.add(CollationEntity
                        .builder()
                        .collation(resultSet.getString("Collation"))
                        .build());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return collationEntities;
    }

    public static Boolean createDb(Integer id, String dbName, String charset, String collation) throws SQLException {
        return execute(id, "CREATE DATABASE `" + dbName + "` CHARACTER SET " + charset + " COLLATE " + collation);
    }

    public static Boolean deleteDb(Integer id, String dbName) throws SQLException {
        return execute(id, "drop database `" + dbName + "`");
    }

    public static Boolean updateDb(Integer id, String dbName, String charset, String collation) throws SQLException {
        return execute(id, "ALTER DATABASE `" + dbName + "` CHARACTER SET " + charset + " COLLATE " + collation);
    }

    public static Boolean execute(Integer id, String sql) throws SQLException {
        Statement statement = null;
        try {
            statement = ((Connection) Storage.CONNECTION_STORAGE.get(id)).createStatement();
            return statement.execute(sql);
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    public static List<FieldEntity> selectTableStruct(Integer id, String dbName, String tableName) {
        Statement statement = null;
        List<FieldEntity> list = new ArrayList<>();
        try {
            statement = ((Connection) Storage.CONNECTION_STORAGE.get(id)).createStatement();
            ResultSet resultSet = statement.executeQuery(
                    "SELECT COLUMN_NAME, COLUMN_TYPE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT, COLUMN_KEY" +
                            " FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = '" + dbName + "' AND table_name = '" + tableName + "'");

            Integer key = 0;

            while (resultSet.next()) {
//                list.add(FieldEntity
//                        .builder()
//                        .key(key++)
//                        .fieldName(resultSet.getString("COLUMN_NAME"))
//                        .type(resultSet.getString("DATA_TYPE"))
//                        .isNull(resultSet.getBoolean("IS_NULLABLE"))
//                        .note(resultSet.getString("COLUMN_COMMENT"))
//                        .length(resultSet.getLong("CHARACTER_MAXIMUM_LENGTH"))
//                        .pk(resultSet.getString("COLUMN_KEY").equals("PRI"))
//                        .defaultValue(resultSet.getString("COLUMN_DEFAULT"))
//                        .build());
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return list;
    }

    public static void createTable(Integer id, String dbName, String tableName, List<FieldEntity> list) {
        try {
            execute(id, "use " + dbName);
            String sql = "create table `" + tableName + "` ( ";

            for (FieldEntity fieldEntity : list) {
//                String field = "`" + fieldEntity.getFieldName() + "` " + fieldEntity.getType();
//                field += fieldEntity.getLength() != null ? "(" + fieldEntity.getLength() + ") " : " ";
//                field += !fieldEntity.getIsNull() ? " not null " : " ";
//                field += fieldEntity.getDefaultValue() != null ? !fieldEntity.getDefaultValue().matches("int|long") ?
//                        " default '" + fieldEntity.getDefaultValue() + "'" : " default " + fieldEntity.getDefaultValue() : " ";
//                field += fieldEntity.getNote() != null ? " comment '" + fieldEntity.getNote() + "'" : " ";
//                field += fieldEntity.getPk() ? " AUTO_INCREMENT, PRIMARY KEY (`" + fieldEntity.getFieldName() + "`)" : " ";
//                field += ",";

//                sql += field;
            }

            sql = sql.substring(0, sql.length() - 1);

            sql += ")ENGINE=InnoDB DEFAULT CHARSET=utf8";
            execute(id, sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void addData(Integer id, String dbName, String tableName, Map<String, DataDTO> data) {
        try {
            String parameter = "( ";
            String field = "( ";
            String sql = "insert into " + tableName;
            execute(id, "use " + dbName);

            for (Map.Entry<String, DataDTO> entry : data.entrySet()) {
                field += entry.getKey() + ", ";
                parameter += entry.getValue().getType().matches("[\\w]{0,}int|long[\\w]{0,}") ? entry.getValue().getData() + ", " : " '" + entry.getValue().getData() + "', ";
            }

            parameter = parameter.substring(0, parameter.length() - 2);
            field = field.substring(0, field.length() - 2);
            sql += field + ") values " + parameter + ")";

            execute(id, sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static Boolean testMysqlConnection(String ip, String port, String account, String password) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":" + port, account, password);
            connection.close();
            return true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return false;
    }
}
